package com.two.user.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.two.user.controller.DataBaseUtil;
import com.two.user.dao.UserDao;
import com.two.user.model.User;

public class UserDaoImpl implements UserDao{
	/**查询根据用户名来查询*/
	@Override
	public User selectByname(String username) {
				//获取数据库的连接
				Connection conn = DataBaseUtil.getConnection();
				//准备sql
				String sql = "select * from user where username=? ";
				//获取声明对象
				PreparedStatement ps = null;
				ResultSet rs =null;
				User user = null;
				try {
				 ps = conn.prepareStatement(sql);
				 //补全sql
				 ps.setString(1, username);
				 //执行并返回结果
				 rs =ps.executeQuery();
				 //解析结果集
				 if(rs.next()) { 
					 //如果有数据则转发到首页
					user = new User();
					user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setAge(rs.getInt("age"));
					user.setGender(rs.getString("gender"));
				 }
				}catch (SQLException e) {
					e.printStackTrace();
				}finally {
					//关闭资源
					DataBaseUtil.closeResource(conn, ps, rs);
				}
			
		return user;
	}
	/**添加用户*/
	@Override
	public int addUser(String username, String password,int age, String gender,String remake) {
		//获取数据库的连接
				Connection conn = DataBaseUtil.getConnection();
				//准备sql
				String sql = "insert into user(`username`,`password`,`age`,`gender`,`remake`,`flag`) values(?,?,?,?,?,1)";
				System.out.println(username);
				System.out.println(password);
				//获取声明对象
				PreparedStatement ps = null;
				ResultSet rs =null;
				try {
				 ps = conn.prepareStatement(sql);
				 //补全sql
				 ps.setString(1, username);
				 ps.setString(2, password);
				 ps.setLong(3, age);
				 ps.setString(4, gender);
				 ps.setString(5, remake);
				 
				 //执行sql并更新数据库
				 int n = ps.executeUpdate();
				 return n;
				}catch (SQLException e) {
					e.printStackTrace();
				}finally {
					//关闭资源
					DataBaseUtil.closeResource(conn, ps, null);
					
				}
		return 0;
		/***/
	}
	/**查询所有用户*/
	@Override
	public List<User> selectAll() {
		List<User> list = new ArrayList<User>();
		
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from user where flag = 1 limit 0,10";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setAge(rs.getInt("age"));
				user.setGender(rs.getString("gender"));
				user.setRemake(rs.getString("remake"));
				list.add(user);
				user = null; // 将user对象置为空便于快速回收内存
			}
			
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return list;
	}
	
	/**根据用户id来查询*/
	@Override
	public User selectById(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "select * from user where id = ?";
		PreparedStatement ps = null;
		ResultSet rs= null;
		System.out.println("Dao - username: " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setAge(rs.getInt("age"));
				user.setGender(rs.getString("gender"));
				user.setRemake(rs.getString("remake"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}
	/**删除用户*/
	@Override
	public int deleteUser(int id) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "update user set flag=0 where id=?";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	}
	
	
	/**更新用户信息*/
	@Override
	public int updateUser(int id, String username, String password, int age, String gender,String remake) {
		Connection conn = DataBaseUtil.getConnection();
		String sql = "UPDATE `user` SET `username`=?, `password`=?,"
				+ " `age`=?, `gender`=?, `remake`=? WHERE (`id`=?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setInt(3, age);
			ps.setString(4, gender);
			ps.setString(5, remake);
			ps.setInt(6, id);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DataBaseUtil.closeResource(conn, ps, null);
		}
		return n;
	
	}
	
	
}
